package my.db;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import my.commons.PagerManager;
import my.domain.Page;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * 数据库查询助手
 * 
 * @author Winter Lau<br>
 */
@SuppressWarnings("unchecked")
public class QueryHelper {
	private final static Log log = LogFactory.getLog(QueryHelper.class);
	private final static QueryRunner _g_runner = new QueryRunner();
	private final static ColumnListHandler _g_columnListHandler = new ColumnListHandler() {
		@Override
		protected Object handleRow(ResultSet rs) throws SQLException {
			Object obj = super.handleRow(rs);
			if (obj instanceof BigInteger)
				return ((BigInteger) obj).longValue();
			return obj;
		}

	};
	private final static ScalarHandler _g_scaleHandler = new ScalarHandler() {
		@Override
		public Object handle(ResultSet rs) throws SQLException {
			Object obj = super.handle(rs);
			if (obj instanceof BigInteger)
				return ((BigInteger) obj).longValue();
			return obj;
		}
	};

	private final static List<Class<?>> PrimitiveClasses = new ArrayList<Class<?>>() {
		{
			add(Long.class);
			add(Integer.class);
			add(String.class);
			add(java.util.Date.class);
			add(java.sql.Date.class);
			add(java.sql.Timestamp.class);
		}
	};

	private final static boolean _IsPrimitive(Class<?> cls) {
		return cls.isPrimitive() || PrimitiveClasses.contains(cls);
	}

	/**
	 * 获取数据库连接
	 * 
	 * @return
	 */
	public static Connection getConnection() {
		try {
			return DBManager.getConnection();
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	/**
	 * 读取某个对象
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static <T> T read(Class<T> beanClass, String sql, Object... params) {
		try {
			return (T) _g_runner.query(getConnection(), sql, _IsPrimitive(beanClass) ? _g_scaleHandler : new BeanHandler(beanClass), params);
		} catch (SQLException e) {
			throw new DBException("读取对象出错:", e);
		}
	}

	// public static <T> T read_cache(Class<T> beanClass, String cache, Serializable key, String sql, Object...params) {
	// return null;
	// T obj = (T)CacheManager.get(cache, key);
	// if(obj == null){
	// obj = read(beanClass, sql, params);
	// CacheManager.set(cache, key, (Serializable)obj);
	// }
	// return obj;
	// }

	/**
	 * 对象查询
	 * 
	 * @param <T>
	 * @param beanClass
	 * @param sql
	 * @param params
	 * @return
	 */
	public static <T> List<T> query(Class<T> beanClass, String sql, Object... params) {
		try {
			return (List<T>) _g_runner.query(getConnection(), sql, _IsPrimitive(beanClass) ? _g_columnListHandler : new BeanListHandler(beanClass), params);
		} catch (SQLException e) {
			throw new DBException("对象查询出错:", e);
		}
	}

	public static <T> List<Map<String,Object>> query(String sql, Object... params) {
		try {
			return _g_runner.query(getConnection(), sql, new MapListHandler(), params);
		} catch (SQLException e) {
			throw new DBException("对象查询出错:", e);
		}
	}

	/**
	 * 支持缓存的对象查询
	 * 
	 * @param <T>
	 * @param beanClass
	 * @param cache_region
	 * @param key
	 * @param sql
	 * @param params
	 * @return
	 */
	// public static <T> List<T> query_cache(Class<T> beanClass, String cache_region, Serializable key, String sql, Object...params) {
	// List<T> objs = (List<T>)CacheManager.get(cache_region, key);
	// if(objs == null){
	// objs = query(beanClass, sql, params);
	// CacheManager.set(cache_region, key, (Serializable)objs);
	// }
	// return objs;
	// }

	/**
	 * 分页查询
	 * 
	 * @param <T>
	 * @param beanClass
	 * @param sql
	 * @param page
	 * @param count
	 * @param params
	 * @return
	 */
	public static <T> List<T> query_slice(Class<T> beanClass, String sql, int page, int count, Object... params) {
		if (page < 0 || count < 0)
			throw new IllegalArgumentException("Illegal parameter of 'page' or 'count', Must be positive.");
		int from = (page - 1) * count;
		count = (count > 0) ? count : Integer.MAX_VALUE;
		return query(beanClass, sql + " LIMIT ?,?", ArrayUtils.addAll(params, new Integer[] { from, count }));
	}

	/**
	 * 
	 * @描述
	 * @作者 钟诚
	 * @日期 2011-10-17
	 * @时间 上午12:01:57
	 * @param <T>
	 * @param beanClass
	 * @param sql
	 *            普通sql
	 * @param offset
	 *            起始条目
	 * @param count
	 *            每页大小
	 * @param params
	 *            sql参数
	 * @return
	 */
	public static <T> List<T> query_sliceForPagerTaglib(Class<T> beanClass, String sql, int offset, int count, Object... params) {
		if (offset < 0 || count < 0)
			throw new IllegalArgumentException("Illegal parameter of 'page' or 'count', Must be positive.");
		count = (count > 0) ? count : Integer.MAX_VALUE;
		return query(beanClass, sql + " LIMIT ?,?", ArrayUtils.addAll(params, new Integer[] { offset, count }));
	}

	public static <T> List<T> query_sliceForPagerTaglib(Class<T> beanClass, String sql, Object... params) {
		Page pager = PagerManager.getPager();
		if (pager.getCount() == 0) {
			String sqlCount = "select count(*) from (" + sql + ") t";
			int allpage = (int) QueryHelper.stat(sqlCount);
			PagerManager.getPager().setCount(allpage);
		}
		int offset = pager.getOffset();
		int size = pager.getSize();
		if (offset < 0 || size < 0)
			throw new IllegalArgumentException("Illegal parameter of 'page' or 'count', Must be positive.");
		size = (size > 0) ? size : Integer.MAX_VALUE;
		return query(beanClass, sql + " LIMIT ?,?", ArrayUtils.addAll(params, new Integer[] { offset, size }));
	}

	/**
	 * 
	 * @描述 分页查询
	 * @作者 钟诚
	 * @日期 2011-8-18
	 * @时间 下午03:26:07
	 * @param <T>
	 * @param beanClass
	 * @param sql
	 * @param params
	 * @return
	 */
	// public static <T> List<T> query_slice(Class<T> beanClass, String sql, Object... params) {
	// Page apage=RequestContext.getPage();
	// int page=apage.getCurpage();
	// int count=apage.getAllpage();
	// return query_slice(beanClass, sql, page, count, params) ;
	// }
	/**
	 * 支持缓存的分页查询
	 * 
	 * @param <T>
	 * @param beanClass
	 * @param cache
	 * @param cache_key
	 * @param cache_obj_count
	 * @param sql
	 * @param page
	 * @param count
	 * @param params
	 * @return
	 */
	// public static <T> List<T> query_slice_cache(Class<T> beanClass, String cache, Serializable cache_key, int cache_obj_count, String sql, int page, int
	// count, Object...params) {
	// List<T> objs = (List<T>)CacheManager.get(cache, cache_key);
	// if(objs == null) {
	// objs = query_slice(beanClass, sql, 1, cache_obj_count, params);
	// CacheManager.set(cache, cache_key, (Serializable)objs);
	// }
	// if(objs == null || objs.size()==0)
	// return objs;
	// int from = (page - 1) * count;
	// if(from < 0)
	// return null;
	// if((from+count) > cache_obj_count)//超出缓存的范围
	// return query_slice(beanClass, sql, page, count, params);
	// int end = Math.min(from + count, objs.size());
	// if(from >= end)
	// return null;
	// return objs.subList(from, end);
	// }

	/**
	 * 执行统计查询语句，语句的执行结果必须只返回一个数值
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static long stat(String sql, Object... params) {
		try {
			Number num = (Number) _g_runner.query(getConnection(), sql, _g_scaleHandler, params);
			return (num != null) ? num.longValue() : -1;
		} catch (SQLException e) {
			throw new DBException("统计查询语句出错:", e);
		}
	}

	/**
	 * 执行统计查询语句，语句的执行结果必须只返回一个数值
	 * 
	 * @param cache_region
	 * @param key
	 * @param sql
	 * @param params
	 * @return
	 */
	// public static long stat_cache(String cache_region, Serializable key, String sql, Object...params) {
	// Number value = (Number)CacheManager.get(cache_region, key);
	// if(value == null){
	// value = stat(sql, params);
	// CacheManager.set(cache_region, key, value);
	// }
	// return value.longValue();
	// }

	/**
	 * 执行INSERT/UPDATE/DELETE语句
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int update(String sql, Object... params) {
		try {
			return _g_runner.update(getConnection(), sql, params);
		} catch (SQLException e) {
			throw new DBException("Sql语句出错:", e);
		}
	}

	/**
	 * 批量执行指定的SQL语句
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int[] batch(String sql, Object[][] params) {
		try {
			return _g_runner.batch(getConnection(), sql, params);
		} catch (SQLException e) {
			throw new DBException("批量执行指定的SQL语句出错:", e);
		}
	}

	/**
	 * 
	 * @param <T>
	 * @描述 插入一个对象
	 * @作者 钟诚
	 * @日期 2011-7-27
	 * @时间 上午09:08:28
	 * @return
	 */
	public static <T> int insertBean(String tableName, T bean, String autoKey) {
		try {
			Map<Object, Object> data = BeanUtils.describe(bean);
			// insert into xxx(`yy`,`yyy`,`yyyy`,`y`,`yy`,`yyy`) values(xxx,xxx,xxx,xxx,xx);
			Map<String, Object> result = getInsertSql(tableName, data, autoKey);
			String sql = result.get("sql").toString();
			Object[] params = (Object[]) result.get("params");// 参数
			return _g_runner.update(getConnection(), sql, params);
		} catch (SQLException e) {
			throw new DBException("添加Bean出错:", e);
		} catch (Exception e) {
			throw new DBException("生成sql出错", e);
		}
	}

	private static Map<String, Object> getInsertSql(String tableName, Map<Object, Object> data, String autoKey) throws Exception {
		StringBuffer sql = new StringBuffer("INSERT INTO ");
		if (tableName == null || data == null) {
			log.warn("tableName或data为null");
			return null;
		}
		sql.append(tableName).append("(");
		StringBuffer field = new StringBuffer();// 字段名
		StringBuffer p = new StringBuffer();// 问号
		List<Object> param = new ArrayList<Object>();
		for (Object key : data.keySet()) {
			Object val = data.get(key);
			if (val != null && !"class".equalsIgnoreCase(String.valueOf(key))) {
				if (autoKey != null) {
					if (autoKey.equalsIgnoreCase(String.valueOf(key)))
						continue;
				}
				field.append("`").append(key).append("`,");
				p.append("?,");
				param.add(val);
			}
		}
		sql.append(field.substring(0, Math.max(field.length() - 1, 0))).append(") VALUES(");
		sql.append(p.substring(0, Math.max(p.length() - 1, 0))).append(")");
		Map<String, Object> result = new HashMap<String, Object>();
		result.put("sql", sql.toString());
		result.put("params", param.toArray());
		return result;
	}
}
